﻿using CallCenter.Data.Base;
using CallCenter.Data.Models;
using CallCenter.Data.Utility;
using System;
using System.Data;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Hosting;


namespace CallCenter.Data.BizUtilities
{
    public class ccContactUtility : BaseUtility
    {

        public ccContactUtility(DataManager manager)
            : base(manager)
        { }


        public ReturnMessage<ContactGridViewModel> InsertToContact(ContactGridViewModel model)
        {
            var returnMessage = new ReturnMessage<ContactGridViewModel>();

            int contactType = 7;
            model.companyId = 0;
            model.userId = 0;

            model.email = "";
            model.jobTitle = "";
            model.department = "";
            model.businessPhone = "";
            model.mobilePhone = "";
            model.notes = "";
            model.office = "";
            model.states = 1;
            model.startDate = DateTime.Now;
            Nullable<DateTime> endDate = DateTime.Now.AddYears(-100);
            DateTime birthday = DateTime.Now.AddYears(-100);
            DateTime enteredDate = Convert.ToDateTime(System.DateTime.Now);
            model.fileAs = "";
            model.visibleToOthers = true;
            var PresentId = this.GetContactIdByName(model.lastName + ' ' + model.firstName);
            bool IsPresent = false;
            if (PresentId != 0)
            {
                IsPresent = true;
            }
            var id = this.Insert(model.lastName, model.firstName, model.userId, model.CreatedUser, model.email, model.companyId, contactType, model.jobTitle, model.department, model.startDate, endDate, model.businessPhone, model.mobilePhone, model.office, model.fileAs, model.notes, IsPresent);

            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                //Insert into CompanyContacts
                this.Bridge.CompanyContactsUtility.Insert(model.companyId, model.id);
                //Insert Records to ScratchLog tableInse
                if (!IsPresent)
                {
                    this.Bridge.ScratchUtility.Insert(id, 2, DateTime.Now, 8, "Came from regular Contact Add", true);
                    returnMessage.Message = "Recordhas been  inserted to database";
                }
                if (IsPresent)
                {
                    returnMessage.Message = "ISPRESENT";
                }
                returnMessage.Status = true;

            }
            return returnMessage;
        }



        public ReturnMessage<ContactGridViewModel> UpdateToContact(ContactGridViewModel model)
        {
            var returnMessage = new ReturnMessage<ContactGridViewModel>();

            if (model.startDate.CompareTo(DateTime.MinValue) == 0)
                model.startDate = DateTime.Now;

            model.userId = 0;
            model.email = "";
            model.companyId = 36;
            model.jobTitle = "";
            model.department = "";
            model.businessPhone = "";
            model.mobilePhone = "";
            model.notes = "";
            model.office = "";
            model.startDate = DateTime.Now;
            Nullable<DateTime> endDate = DateTime.Now.AddYears(-100);

            model.fileAs = "";


            if (model.lastName.Length == 0 && model.firstName.Length == 0)
            {
                returnMessage.Message = "Name can not be Empty";
                returnMessage.Status = false;
            }



            var id = this.Update(model.lastName, model.firstName, model.CreatedUser, model.userId, model.email, model.companyId, 1, model.jobTitle, model.department, model.startDate, endDate, model.businessPhone, model.mobilePhone, model.office, model.notes, model.fileAs, model.id);

            if (id == -1)
            {
                returnMessage.Message = "We cannot update data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
                this.Bridge.ContactDetailUtility.Insert(model.id, 36, "", 1, 1, "",
                    "", "", model.email, 1, model.startDate, endDate, model.notes);
            }

            return returnMessage;
        }




        //define placeholders for the values in the table to insert
        public int Insert(int userId, int companyId, string title, string LastName, string FirstName, string MiddleName, string suffix, string fileAs, string Email, string website, string businessPhone, string mobilePhone, string businessFax, string streetOne, string streetTwo, string city, int state, string zip, string country, bool mailingAddress, string jobTitle, string department,
    string office, string profession, string managerName, string assistantName, string spouse, DateTime birthday, int EnteredBy, DateTime enteredDate, int contactType, bool visibleToOthers, DateTime startDate, Nullable<DateTime> endDate, string notes)
        {

            int nRtn = -1;

            string sql = "INSERT INTO ccContacts " +
                  "(userId, companyId, lastname, firstname, middleName, fileAs, Email, website, businessPhone, mobilePhone," +
                  " businessFax, streetOne, streetTwo, city, state, zip, country, mailingAddress, jobTitle, department, office," +
                  " profession, managerName, assistantName, spouse, birthday, EnteredById, enteredDate, contactType, visibleToOthers,startDate, endDate, notes) " +
                  "VALUES(@userId, @companyId, @lastname, @firstname, @middleName, @fileAs, @Email, @website, @businessPhone, @mobilePhone, " +
                  "@businessFax, @streetOne, @streetTwo, @city, @state, @zip, @country, @mailingAddress, @jobTitle, @department, @office, " +
                  "@profession, @managerName, @assistantName, @spouse, @birthday, @EnteredBy, @enteredDate, @contactType, @visibleToOthers,@startDate,@endDate, @notes) " +
                  "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@businessPhone", SqlDbType.NVarChar, 100, businessPhone);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                //  this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                // this.Factory.AddParameter(cmd, "@date", SqlDbType.DateTime, 0, date);
                // this.Factory.AddParameter(cmd, "@via", SqlDbType.Int, 10, via);
                this.Factory.AddParameter(cmd, "@lastName", SqlDbType.NVarChar, 1000, LastName);
                this.Factory.AddParameter(cmd, "@firstName", SqlDbType.NVarChar, 7, FirstName);
                this.Factory.AddParameter(cmd, "@middleName", SqlDbType.NVarChar, 10, MiddleName);
                this.Factory.AddParameter(cmd, "@fileAs", SqlDbType.NVarChar, 10, fileAs);
                this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 0, Email);
                this.Factory.AddParameter(cmd, "@website", SqlDbType.NVarChar, 0, website);
                this.Factory.AddParameter(cmd, "@mobilePhone", SqlDbType.NVarChar, 10, mobilePhone);
                this.Factory.AddParameter(cmd, "@businessFax", SqlDbType.NVarChar, 1000, businessFax);
                this.Factory.AddParameter(cmd, "@streetOne", SqlDbType.NVarChar, 7, streetOne);
                this.Factory.AddParameter(cmd, "@streetTwo", SqlDbType.NVarChar, 10, streetTwo);
                this.Factory.AddParameter(cmd, "@city", SqlDbType.NVarChar, 10, city);
                this.Factory.AddParameter(cmd, "@state", SqlDbType.NVarChar, 0, state);
                this.Factory.AddParameter(cmd, "@zip", SqlDbType.Int, 10, zip);
                this.Factory.AddParameter(cmd, "@country", SqlDbType.NVarChar, 10, country);
                this.Factory.AddParameter(cmd, "@mailingAddress", SqlDbType.NVarChar, 50, mailingAddress);
                this.Factory.AddParameter(cmd, "@jobTitle", SqlDbType.NVarChar, 10, jobTitle);
                this.Factory.AddParameter(cmd, "@department", SqlDbType.NVarChar, 10, department);
                this.Factory.AddParameter(cmd, "@office", SqlDbType.NVarChar, 0, office);
                this.Factory.AddParameter(cmd, "@profession", SqlDbType.NVarChar, 50, profession);
                this.Factory.AddParameter(cmd, "@managerName", SqlDbType.NVarChar, 50, managerName);
                this.Factory.AddParameter(cmd, "@assistantName", SqlDbType.NVarChar, 50, assistantName);
                this.Factory.AddParameter(cmd, "@spouse", SqlDbType.NVarChar, 10, spouse);
                this.Factory.AddParameter(cmd, "@birthday", SqlDbType.DateTime, 10, birthday);
                this.Factory.AddParameter(cmd, "@contactType", SqlDbType.Int, 10, contactType);
                this.Factory.AddParameter(cmd, "@visibleToOthers", SqlDbType.Bit, 1000, visibleToOthers);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@enteredBy", SqlDbType.DateTime, 10, EnteredBy);
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@enteredDate", SqlDbType.DateTime, 10, enteredDate);

                //this.Factory.ExecuteNonQuery(cmd);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);


                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn; //retruning the data stored in the table
        }




        public int Insert(string LastName, string FirstName, int userId, string CreatedUser, string email,
                          int companyId, int contactType, string jobTitle,
                          string department, DateTime startDate, Nullable<DateTime> endDate,
                          string businessPhone, string mobilePhone, string office, string fileAs,
                          string notes, bool IsPresent
            )
        {

            int nRtn = -1;

            string sql = "INSERT INTO ccContacts " +
                "(companyId, lastname, firstname, userId, CreatedUser, Email, businessPhone, mobilePhone," +
                " jobTitle, department, office," +
                " enteredDate, contactType, startDate, endDate, fileAs, notes,IsPresent) " +
                "VALUES( @companyId, @lastname, @firstname, @userId, @CreatedUser, @Email,@businessPhone, @mobilePhone, " +
                " @jobTitle, @department, @office, " +
                " @enteredDate, @contactType, @startDate,@endDate,@fileAs, @notes,@IsPresent) " +
                "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@businessPhone", SqlDbType.NVarChar, 100, businessPhone);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@lastName", SqlDbType.NVarChar, 1000, LastName);
                this.Factory.AddParameter(cmd, "@firstName", SqlDbType.NVarChar, 7, FirstName);
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@CreatedUser", SqlDbType.NVarChar, 50, CreatedUser);
                this.Factory.AddParameter(cmd, "@fileAs", SqlDbType.NVarChar, 255, fileAs);
                this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 0, email);
                this.Factory.AddParameter(cmd, "@mobilePhone", SqlDbType.NVarChar, 10, mobilePhone);
                this.Factory.AddParameter(cmd, "@jobTitle", SqlDbType.NVarChar, 10, jobTitle);
                this.Factory.AddParameter(cmd, "@department", SqlDbType.NVarChar, 10, department);
                this.Factory.AddParameter(cmd, "@office", SqlDbType.NVarChar, 0, office);
                this.Factory.AddParameter(cmd, "@contactType", SqlDbType.Int, 10, contactType);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@IsPresent", SqlDbType.Bit, 10, IsPresent);

                this.Factory.AddParameter(cmd, "@enteredDate", SqlDbType.DateTime, 0, DateTime.Now);
                if (startDate.CompareTo(DateTime.MinValue) == 0)
                    startDate = DateTime.Now;
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);
                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 0, DBNull.Value);
                //set to null value
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;
                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }



        public void Delete(int nContactID) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ccContacts " +
                                     "WHERE ID = @ID " +
                                     "DELETE FROM ddlCompanyContacts " +
                 "WHERE contactId = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nContactID);
                this.Factory.ExecuteNonQuery(cmd);
            }


        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM ccContacts ";

            this.Factory.ExecuteNonQuery(sql);
        }

        public List<ContactGridViewModel> GetAll()
        {
            string sql = "SELECT cs.id as id, cs.firstName+' '+ cs.lastName as name, cs.*, " +
                 " cs.businessPhone, cs.companyId as companyId, cs.email, cp.name as cpName,   ct.description as contactTypeName, " +
                 " CONVERT(date, cs.enteredDate) as callDate from ccContacts cs " +
                 " LEFT JOIN ddlCompany cp on cp.id = cs.companyId " +

                 " LEFT JOIN ddlContactTypes ct on ct.id= cs.contactType WHERE cs.id <> 73  order by   cs.startDate desc ";

            return this.Factory.ExecuteObjects<ContactGridViewModel>(sql);

        }

        public List<ContactGridViewModel> GetAllContactByLoggedInUser(string UserName)
        {
            string sql = "SELECT cs.id as id, cs.firstName+' '+ cs.lastName as name, cs.*, " +
                 " cs.businessPhone, cs.companyId as companyId, cs.email, cp.name as cpName,   ct.description as contactTypeName, " +
                 " CONVERT(date, cs.enteredDate) as callDate from ccContacts cs " +
                 " LEFT JOIN ddlCompany cp on cp.id = cs.companyId " +

                 " LEFT JOIN ddlContactTypes ct on ct.id= cs.contactType WHERE lower(cs.createdUser) = lower('" + UserName + "')  and cs.startDate = convert(date, GETDATE())    order by   cs.startDate desc ";

            return this.Factory.ExecuteObjects<ContactGridViewModel>(sql);

        }


        public List<ContactGridViewModel> GetAll(string loggedUser)
        {
            string sql = "SELECT cs.id as id, cs.firstName+' '+ cs.lastName as name, cs.*, " +
                 " cs.businessPhone, cs.companyId as companyId, cs.email, cp.name as cpName,   ct.description as contactTypeName, " +
                 " CONVERT(date, cs.enteredDate) as callDate from ccContacts cs " +
                 " LEFT JOIN ddlCompany cp on cp.id = cs.companyId " +

                 " LEFT JOIN ddlContactTypes ct on ct.id= cs.contactType WHERE Lower(cs.CreatedUser) =  '" + loggedUser.ToLower() + "' order by   cs.id desc";

            return this.Factory.ExecuteObjects<ContactGridViewModel>(sql);

        }

        public DataTable GetJustName()
        {
            string sql = " SELECT id, lastname as name from ccContacts where lastname like 'None' UNION ALL " +
                 " SELECT id,  lastname+', '+firstname as name FROM ccContacts  where lastName <> 'None' ";

            return this.Factory.ExecuteTable(sql);

        }
        public int GetContactIdByName(string name)
        {
            int result = 0;
            string sql = string.Format("select id from ccContacts where lastName + ' ' + firstName = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }

        public int GetContactIdByScName(string name)
        {
            int result = 0;
            string sql = string.Format("select id from ccContacts where lastName+','  +  firstName = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }

        public int GetEmployeeIdByName(string name)
        {
            int result = 0;
            string sql = string.Format("select id from ccContacts where firstName + ' '+ lastName = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }

        public int GetEmployeeIdByNameforProduct(string name)
        {
            int result = 0;
            string sql = string.Format("select id from ccContacts where lastname + ', '+ firstname = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }

        public int GetHeldIdByName(string name)
        {
            int result = 0;
            string sql = string.Format("select id from ccContacts where lastName + ' '+ firstName = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }
        public DataTable GetName(int contactId)
        {
            string sql = "SELECT firstName+' '+ lastName as name FROM ccContacts Where id = " + contactId;

            return this.Factory.ExecuteTable(sql);
        }

        public DataTable GetEmails(int contactId)
        {
            string sql = " SELECT id, firstName + ' ' + lastName as name, email from ccContacts where id = " + contactId;

            return this.Factory.ExecuteTable(sql);

        }



        public DataTable GetContactEmails(int companyId)
        {
            string sql = " SELECT id, firstName + ' ' + lastName as name, email from ccContacts where companyId = " + companyId;

            return this.Factory.ExecuteTable(sql);

        }

        public DataTable GetMinimumContact()
        {
            string sql = "GetMinimumContact";

            return this.Factory.ExecuteTable(sql);

        }


        public List<ContactGridViewModel> GetAllContacts()
        {

            string sql = "SELECT distinct c.lastname + ', ' + c.firstname as contactname, c.id FROM ccContacts c ";
            return this.Factory.ExecuteObjects<ContactGridViewModel>(sql);
        }



        public List<ContactDetailViewModel> GetContactDetails()
        {
            string sql = "SELECT distinct c.id as ContactId, c.businessPhone as businessPhone,c.email, c.lastname + ', ' + c.firstname as name,  dc.name as company,c.fileAs as photo FROM ccContacts c INNER JOIN ddlEmployees e ON e.contactId<> c.id LEFT JOIN ddlCompany dc ON dc.id = c.companyId";

            return this.Factory.ExecuteObjects<ContactDetailViewModel>(sql);

        }

        public List<ContactDetailViewModel> GetContactSDetails()
        {
            string sql = "  SELECT distinct c.id as ContactId, c.businessPhone as businessPhone,c.email, c.lastname + ','+c.firstname as name,  dc.name as company,c.fileAs as photo FROM ccContacts c INNER JOIN ddlEmployees e ON e.contactId<> c.id LEFT JOIN ddlCompany dc ON dc.id = c.companyId";

            return this.Factory.ExecuteObjects<ContactDetailViewModel>(sql);

        }


        public List<CallertDetailViewModel> GetCallerDetails()
        {

            string sql = "SELECT  distinct a.caller, b.name,  a.caller + ',' + b.name as callercompany FROM ScratchCallLogs a  JOIN  ddlCompany b ON a.companyid = b.id";

            return this.Factory.ExecuteObjects<CallertDetailViewModel>(sql);

        }

        public int Update(int userId, int Id, bool visibleToOthers)
        {

            int nRtn = -1;

            string sql = "UPDATE ccContacts " +
                 "SET" +
                 " visibleToOthers = @visibleToOthers WHERE userId=@userId AND Id=@Id";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.NVarChar, 100, userId);
                this.Factory.AddParameter(cmd, "@Id", SqlDbType.Int, 10, Id);
                this.Factory.AddParameter(cmd, "@visibleToOthers", SqlDbType.Int, 10, visibleToOthers);
                return this.Factory.ExecuteNonQuery(cmd);

            }


            // return nRtn; //retruning the data stored in the table
        }

        public int Update(int id, int userId, int companyId, string title, string LastName, string FirstName, string MiddleName, string suffix, string fileAs, string Email, string website, string businessPhone, string mobilePhone, string businessFax, string streetOne, string streetTwo, string city, int state, string zip, string country, bool mailingAddress, string jobTitle, string department, string office, string profession, string managerName, string assistantName, string spouse, DateTime birthday, int EnteredBy, DateTime enteredDate, int contactType, bool visibleToOthers, DateTime startDate, Nullable<DateTime> endDate, string notes
          )
        {

            int nRtn = -1;
            string sql = "UPDATE ccContacts " +
                   " SET " +
                   " userId=@userId, companyId=@CompanyId, title=@title, lastname=@lastname, firstname=@firstName, middleName=@middleName, " +
                   " suffix=@suffix, fileAs=@fileAs, Email=@email, website=@website, businessPhone=@businessPhone, mobilePhone=@mobilePhone, businessFax=@businessFax, " +
                   " streetOne=@streetOne, streetTwo=@streetTwo, city=@city, state=@state, zip=@zip, country=@country, mailingAddress=@mailingAddress, " +
                   " jobTitle=@jobTitle, department=@department, office=@office, profession=@profession, managerName=@managerName, assistantName=@assistantName, " +
                   " spouse=@spouse, birthday=@birthday, EnteredById=@EnteredBy, enteredDate=@enteredDate, contactType=@contactType, visibleToOthers=@visibleToOthers," +
                   " startDate = @startDate, endDate = @endDate, notes=@notes WHERE id=@id";

            {
                using (var cmd = this.Factory.GetCommand(sql))
                {
                    this.Factory.AddParameter(cmd, "@userId", SqlDbType.NVarChar, 100, userId);
                    this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                    this.Factory.AddParameter(cmd, "@title", SqlDbType.Int, 10, title);
                    this.Factory.AddParameter(cmd, "@lastName", SqlDbType.NVarChar, 100, LastName);
                    this.Factory.AddParameter(cmd, "@firstName", SqlDbType.Int, 10, FirstName);
                    this.Factory.AddParameter(cmd, "@middleName", SqlDbType.Int, 10, MiddleName);
                    this.Factory.AddParameter(cmd, "@suffix", SqlDbType.NVarChar, 100, suffix);
                    this.Factory.AddParameter(cmd, "@fileAs", SqlDbType.NVarChar, 10, fileAs);
                    this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 10, Email);
                    this.Factory.AddParameter(cmd, "@website", SqlDbType.NVarChar, 100, website);
                    this.Factory.AddParameter(cmd, "@businessPhone", SqlDbType.Int, 10, businessPhone);
                    this.Factory.AddParameter(cmd, "@mobilePhone", SqlDbType.Int, 10, mobilePhone);
                    this.Factory.AddParameter(cmd, "@businessFax", SqlDbType.NVarChar, 100, businessFax);
                    this.Factory.AddParameter(cmd, "@streetOne", SqlDbType.Int, 10, streetOne);
                    this.Factory.AddParameter(cmd, "@streetTwo", SqlDbType.Int, 10, streetTwo);
                    this.Factory.AddParameter(cmd, "@city", SqlDbType.Int, 10, city);
                    this.Factory.AddParameter(cmd, "@state", SqlDbType.NVarChar, 100, state);
                    this.Factory.AddParameter(cmd, "@zip", SqlDbType.Int, 10, zip);
                    this.Factory.AddParameter(cmd, "@country", SqlDbType.Int, 10, country);
                    this.Factory.AddParameter(cmd, "@mailingAddress", SqlDbType.Int, 10, mailingAddress);
                    this.Factory.AddParameter(cmd, "@jobTitle", SqlDbType.Int, 10, jobTitle);
                    this.Factory.AddParameter(cmd, "@department", SqlDbType.Int, 10, department);
                    this.Factory.AddParameter(cmd, "@office", SqlDbType.NVarChar, 100, office);
                    this.Factory.AddParameter(cmd, "@profession", SqlDbType.Int, 10, profession);
                    this.Factory.AddParameter(cmd, "@managerName", SqlDbType.NVarChar, 10, managerName);
                    this.Factory.AddParameter(cmd, "@assistantName", SqlDbType.NVarChar, 100, assistantName);
                    this.Factory.AddParameter(cmd, "@spouse", SqlDbType.VarChar, 10, spouse);
                    this.Factory.AddParameter(cmd, "@birthday", SqlDbType.SmallDateTime, 10, birthday);
                    this.Factory.AddParameter(cmd, "@contactType", SqlDbType.NVarChar, 100, contactType);
                    this.Factory.AddParameter(cmd, "@enteredDate", SqlDbType.DateTime, 10, enteredDate);
                    this.Factory.AddParameter(cmd, "@visibleToOthers", SqlDbType.Int, 10, visibleToOthers);
                    this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                    this.Factory.AddParameter(cmd, "@enteredBy", SqlDbType.VarChar, 10, EnteredBy);
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.Int, 10, startDate);
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);
                    this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                    return this.Factory.ExecuteNonQuery(cmd);

                    //if (startDate.CompareTo(DateTime.MinValue) == 0)
                    //    startDate = DateTime.Now;
                    //cmd.Parameters["@startDate"].Value = startDate;


                    //if (endDate.Equals(DateTime.MinValue))
                    //    cmd.Parameters["@endDate"].Value = DBNull.Value;      //set to null value

                    //cmd.Parameters["@endDate"].Value = enteredDate;

                }
            }





            //  return nRtn; //retruning the data stored in the table
        }

        public int Update(string LastName, string FirstName, string CreatedUser, int userId, string email,
                         int companyId, int contactType, string jobTitle,
                         string department, DateTime startDate, Nullable<DateTime> endDate,
                         string businessPhone, string mobilePhone, string office,
                         string notes, string fileAs, int id
           )
        {

            int nRtn = -1;

            string sql = "UPDATE ccContacts " +
                 " SET " +
                 "  companyId=@CompanyId,  lastname=@lastname, firstname=@firstName, CreatedUser=@CreatedUser, userId=@userId , " +
                 " Email=@email, businessPhone=@businessPhone, mobilePhone=@mobilePhone,  " +
                 " jobTitle=@jobTitle, department=@department, office=@office, " +
                 "  contactType=@contactType, fileAs=@fileAs," +
                 " startDate = @startDate, endDate = @endDate, notes=@notes WHERE id=@id";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@lastName", SqlDbType.NVarChar, 100, LastName);
                this.Factory.AddParameter(cmd, "@firstName", SqlDbType.NVarChar, 20, FirstName);
                this.Factory.AddParameter(cmd, "@CreatedUser", SqlDbType.NVarChar, 50, CreatedUser);
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.NVarChar, 10, userId);
                this.Factory.AddParameter(cmd, "@fileAs", SqlDbType.NVarChar, 10, fileAs);
                this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 10, email);
                this.Factory.AddParameter(cmd, "@businessPhone", SqlDbType.NVarChar, 10, businessPhone);
                this.Factory.AddParameter(cmd, "@mobilePhone", SqlDbType.NVarChar, 10, mobilePhone);
                this.Factory.AddParameter(cmd, "@jobTitle", SqlDbType.NVarChar, 10, jobTitle);
                this.Factory.AddParameter(cmd, "@department", SqlDbType.NVarChar, 10, department);
                this.Factory.AddParameter(cmd, "@office", SqlDbType.NVarChar, 100, office);
                this.Factory.AddParameter(cmd, "@contactType", SqlDbType.Int, 100, contactType);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@enteredDate", SqlDbType.DateTime, 10, DateTime.Now);

                this.Factory.ExecuteNonQuery(cmd);

                //if (startDate.CompareTo(DateTime.MinValue) == 0)
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DateTime.Now);

                //if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                //    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);

                //else

                //    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);
            }

            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }

        public int UpdateccContactsExceptName(string email,
                        int companyId, int contactType,
                         Nullable<DateTime> startDate,
                        string businessPhone, string mobilePhone, string office,
                        string notes, int id, string JobTitle
          )
        {

            int nRtn = -1;

            string sql = "UPDATE ccContacts " +
                 " SET " +
                 "  companyId=@CompanyId,   " +
                 " Email=@email, businessPhone=@businessPhone, mobilePhone=@mobilePhone,  " +
                 "  contactType=@contactType,JobTitle = @JobTitle, " +
                 " startDate = @startDate,  notes=@notes WHERE id=@id";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 100, email);
                this.Factory.AddParameter(cmd, "@businessPhone", SqlDbType.NVarChar, 10, businessPhone);
                this.Factory.AddParameter(cmd, "@mobilePhone", SqlDbType.NVarChar, 10, mobilePhone);
                this.Factory.AddParameter(cmd, "@contactType", SqlDbType.Int, 100, contactType);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                //  this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);
                this.Factory.AddParameter(cmd, "@JobTitle", SqlDbType.NVarChar, 500, JobTitle);



                //if (startDate.CompareTo(DateTime.MinValue) == 0)
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DateTime.Now);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);

                else

                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                this.Factory.ExecuteNonQuery(cmd);
            }

            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


        public ContactDetailViewModel GetContactDetailByContactId(int ContactId)
        {
            string sql = "Select * from cccontacts where id = '" + ContactId + "'";

            return this.Factory.ExecuteObject<ContactDetailViewModel>(sql);
        }

        public List<ContactGridViewModel> GetContactDetailByNameandID(ContactGridViewModel model)
        {
            string sql = "Select cc.*,(select top(1) name from ddlCompany as dc where dc.id = cc.companyid) as Company  from cccontacts as cc where (cc.ispresent = 0 or cc.IsPresent is null) and lower(lastname+firstname)  = lower('" + model.lastName + model.firstName + "')";

            return this.Factory.ExecuteObjects<ContactGridViewModel>(sql);
        }

        public string SaveIsPresentContact(int id)
        {
            string sql = "update cccontacts set ispresent = null where id = '" + id + "'";
            this.Factory.ExecuteNonQuery(sql);
            this.Bridge.ScratchUtility.Insert(id, 2, DateTime.Now, 8, "Came from regular Contact Add", true);
            return "SUCCESS";
        }

        public string DeletePresentContact(int id)
        {
            string sql = "delete cccontacts where ispresent is not null and id = '" + id + "'";
            this.Factory.ExecuteNonQuery(sql);
            return "SUCCESS";
        }
    }
}
